Common SQL Queries

Overview

This page provides common SQL query patterns for analyzing Steam Market data across all tables. All examples use the item "AK-47 | Redline (Field-Tested)" - replace with your tracked items.

Connecting to the Database

sqlite3 data/market_data.db

All timestamps are stored in UTC. Remember to adjust for your local timezone when interpreting results.

Basic Queries

Latest Price
Recent Prices
Current Spread
Recent Trades

Get the most recent price snapshot for an item:

SELECT timestamp, lowest_price, median_price, volume
FROM price_overview
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
ORDER BY timestamp DESC
LIMIT 1;

Get all price updates from the last hour:

SELECT timestamp, lowest_price, median_price
FROM price_overview
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
  AND timestamp > datetime('now', '-1 hour')
ORDER BY timestamp DESC;

Get the current bid-ask spread:

SELECT timestamp, 
       highest_buy_order AS bid,
       lowest_sell_order AS ask,
       (lowest_sell_order - highest_buy_order) AS spread
FROM orders_histogram
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
ORDER BY timestamp DESC
LIMIT 1;

Get the latest trade activity:

SELECT timestamp, parsed_activities, activity_count
FROM orders_activity
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
ORDER BY timestamp DESC
LIMIT 10;

Historical Analysis

Daily Averages
Hourly Trend
Price Spikes
Weekly Summary

Calculate daily average price over the last 30 days:

SELECT date(time) AS day,
       AVG(price) AS avg_price,
       SUM(volume) AS total_volume
FROM price_history
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
  AND time > datetime('now', '-30 days')
GROUP BY date(time)
ORDER BY day DESC;

Get hourly price trend for today:

SELECT strftime('%H:00', time) AS hour, price, volume
FROM price_history
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
  AND date(time) = date('now')
ORDER BY time;

Find prices 20% above average:

WITH avg_price AS (
    SELECT AVG(price) AS mean 
    FROM price_history
    WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
)
SELECT time, price, volume,
       (price - mean) / mean * 100 AS spike_pct
FROM price_history, avg_price
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
  AND price > mean * 1.2
ORDER BY time DESC;

Weekly price statistics:

SELECT 
    strftime('%Y-W%W', time) AS week,
    AVG(price) AS avg_price,
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    SUM(volume) AS total_volume
FROM price_history
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
  AND time > datetime('now', '-90 days')
GROUP BY strftime('%Y-W%W', time)
ORDER BY week DESC;

Order Book Analysis

Current Order Book
Order Depth
Spread Analysis
Top Buy Orders

Get full order book (latest snapshot):

SELECT timestamp, buy_order_table, sell_order_table
FROM orders_histogram
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
ORDER BY timestamp DESC
LIMIT 1;

Track buy/sell order counts over time:

SELECT timestamp, 
       buy_order_count, 
       sell_order_count,
       (buy_order_count + sell_order_count) AS total_orders
FROM orders_histogram
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
  AND timestamp > datetime('now', '-24 hours')
ORDER BY timestamp;

Analyze spread volatility over time:

SELECT 
    date(timestamp) AS day,
    AVG(lowest_sell_order - highest_buy_order) AS avg_spread,
    MIN(lowest_sell_order - highest_buy_order) AS min_spread,
    MAX(lowest_sell_order - highest_buy_order) AS max_spread
FROM orders_histogram
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
  AND timestamp > datetime('now', '-30 days')
GROUP BY date(timestamp)
ORDER BY day DESC;

Extract top 5 buy orders using JSON:

SELECT 
    timestamp,
    json_extract(buy_order_table, '$[0].price') AS bid_1,
    json_extract(buy_order_table, '$[1].price') AS bid_2,
    json_extract(buy_order_table, '$[2].price') AS bid_3,
    json_extract(buy_order_table, '$[3].price') AS bid_4,
    json_extract(buy_order_table, '$[4].price') AS bid_5
FROM orders_histogram
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
ORDER BY timestamp DESC 
LIMIT 1;

Activity Analysis

Activity Volume
Activity Types
Peak Hours
Individual Trades

Track activity volume over time:

SELECT 
    strftime('%Y-%m-%d %H:00', timestamp) AS hour,
    SUM(activity_count) AS total_activities,
    COUNT(*) AS snapshots_captured
FROM orders_activity
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
  AND timestamp > datetime('now', '-24 hours')
GROUP BY strftime('%Y-%m-%d %H:00', timestamp)
ORDER BY hour DESC;

Count activities by type:

SELECT 
    date(timestamp) AS day,
    json_each.value ->> '$.action' AS action_type,
    COUNT(*) AS occurrences
FROM orders_activity,
     json_each(parsed_activities)
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
  AND timestamp > datetime('now', '-7 days')
GROUP BY date(timestamp), action_type
ORDER BY day DESC, occurrences DESC;

Find most active trading periods:

SELECT 
    strftime('%H', timestamp) AS hour_of_day,
    AVG(activity_count) AS avg_activity,
    SUM(activity_count) AS total_activity
FROM orders_activity
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
  AND timestamp > datetime('now', '-30 days')
GROUP BY strftime('%H', timestamp)
ORDER BY avg_activity DESC;

Extract individual activities from JSON:

SELECT 
    timestamp,
    json_each.value ->> '$.price' AS trade_price,
    json_each.value ->> '$.action' AS trade_action,
    json_each.value ->> '$.timestamp' AS trade_time
FROM orders_activity,
     json_each(parsed_activities)
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
  AND id = (SELECT MAX(id) FROM orders_activity 
            WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)')
ORDER BY trade_time DESC;

Cross-Item Comparisons

Price Comparison
Volatility Ranking
Volume Leaders
Market Activity

Compare current prices across all tracked items:

SELECT market_hash_name,
       MAX(timestamp) AS last_update,
       lowest_price,
       volume
FROM price_overview
GROUP BY market_hash_name
ORDER BY volume DESC;

Most volatile items by price range in last 24h:

SELECT market_hash_name,
       MIN(price) AS low,
       MAX(price) AS high,
       MAX(price) - MIN(price) AS range,
       (MAX(price) - MIN(price)) / AVG(price) * 100 AS volatility_pct
FROM price_history
WHERE time > datetime('now', '-24 hours')
GROUP BY market_hash_name
ORDER BY volatility_pct DESC;

Items with highest trading volume:

SELECT 
    market_hash_name,
    SUM(volume) AS total_volume_24h,
    AVG(price) AS avg_price_24h
FROM price_history
WHERE time > datetime('now', '-24 hours')
GROUP BY market_hash_name
ORDER BY total_volume_24h DESC
LIMIT 10;

Compare activity levels across items:

SELECT 
    market_hash_name,
    SUM(activity_count) AS total_activities,
    COUNT(*) AS snapshots
FROM orders_activity
WHERE timestamp > datetime('now', '-24 hours')
GROUP BY market_hash_name
ORDER BY total_activities DESC;

Advanced Queries

Moving Average
Price Momentum
Correlation
Bollinger Bands

7-day simple moving average:

SELECT 
    time,
    price,
    AVG(price) OVER (
        ORDER BY time
        ROWS BETWEEN 167 PRECEDING AND CURRENT ROW
    ) AS sma_7day
FROM price_history
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
  AND time > datetime('now', '-30 days')
ORDER BY time DESC;

168 hours = 7 days × 24 hours (7-day moving average)

Calculate price momentum (rate of change):

WITH lagged_prices AS (
    SELECT 
        time,
        price,
        LAG(price, 24) OVER (ORDER BY time) AS price_24h_ago
    FROM price_history
    WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
      AND time > datetime('now', '-7 days')
)
SELECT 
    time,
    price,
    price_24h_ago,
    (price - price_24h_ago) / price_24h_ago * 100 AS momentum_pct
FROM lagged_prices
WHERE price_24h_ago IS NOT NULL
ORDER BY time DESC;

Price correlation between two items:

WITH item1 AS (
    SELECT time, price AS price1
    FROM price_history
    WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
),
item2 AS (
    SELECT time, price AS price2
    FROM price_history
    WHERE market_hash_name = 'AWP | Asiimov (Field-Tested)'
)
SELECT 
    COUNT(*) AS observations,
    AVG(price1) AS avg_item1,
    AVG(price2) AS avg_item2,
    (SUM(price1 * price2) - SUM(price1) * SUM(price2) / COUNT(*)) /
    (SQRT(SUM(price1 * price1) - SUM(price1) * SUM(price1) / COUNT(*)) *
     SQRT(SUM(price2 * price2) - SUM(price2) * SUM(price2) / COUNT(*))) AS correlation
FROM item1 JOIN item2 USING (time)
WHERE item1.time > datetime('now', '-30 days');

Calculate Bollinger Bands (20-period, 2 std dev):

WITH stats AS (
    SELECT 
        time,
        price,
        AVG(price) OVER (
            ORDER BY time
            ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
        ) AS sma_20,
        STDEV(price) OVER (
            ORDER BY time
            ROWS BETWEEN 19 PRECEDING AND CURRENT ROW
        ) AS stddev_20
    FROM price_history
    WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
      AND time > datetime('now', '-30 days')
)
SELECT 
    time,
    price,
    sma_20 AS middle_band,
    sma_20 + (2 * stddev_20) AS upper_band,
    sma_20 - (2 * stddev_20) AS lower_band
FROM stats
ORDER BY time DESC;

JSON Manipulation

Extract Array
Array Length
Filter JSON
Aggregate JSON

Extract all buy orders as individual rows:

SELECT 
    timestamp,
    json_each.key AS order_index,
    json_each.value ->> '$.price' AS price,
    json_each.value ->> '$.quantity' AS quantity
FROM orders_histogram,
     json_each(buy_order_table)
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
  AND id = (SELECT MAX(id) FROM orders_histogram 
            WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)')
ORDER BY price DESC;

Count items in JSON arrays:

SELECT 
    timestamp,
    json_array_length(buy_order_table) AS buy_orders,
    json_array_length(sell_order_table) AS sell_orders
FROM orders_histogram
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
ORDER BY timestamp DESC
LIMIT 10;

Find activities above a certain price:

SELECT 
    timestamp,
    json_each.value ->> '$.price' AS price,
    json_each.value ->> '$.action' AS action
FROM orders_activity,
     json_each(parsed_activities)
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
  AND CAST(REPLACE(REPLACE(json_each.value ->> '$.price', '$', ''), ',', '') AS REAL) > 5.0
ORDER BY timestamp DESC;

Aggregate statistics from JSON data:

SELECT 
    market_hash_name,
    COUNT(*) AS total_activities,
    json_each.value ->> '$.action' AS action_type,
    AVG(CAST(REPLACE(REPLACE(json_each.value ->> '$.price', '$', ''), ',', '') AS REAL)) AS avg_price
FROM orders_activity,
     json_each(parsed_activities)
WHERE timestamp > datetime('now', '-7 days')
GROUP BY market_hash_name, action_type
ORDER BY market_hash_name, total_activities DESC;

Export & Backup

Export to CSV

# Export all price history
sqlite3 -header -csv data/market_data.db \
  "SELECT * FROM price_history" > price_history.csv

# Export specific item
sqlite3 -header -csv data/market_data.db \
  "SELECT * FROM price_history WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'" \
  > ak47_redline.csv

Backup Database

# Create backup
sqlite3 data/market_data.db ".backup data/market_data_backup.db"

# Restore from backup
sqlite3 data/market_data.db ".restore data/market_data_backup.db"

Performance Tips

Use EXPLAIN QUERY PLAN

Check if your queries are using indexes efficiently:

EXPLAIN QUERY PLAN
SELECT * FROM price_overview 
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
ORDER BY timestamp DESC
LIMIT 1;

Look for “USING INDEX” in the output.

Limit Result Sets

Always use LIMIT when you don’t need all rows:

-- Good: Only fetch what you need
SELECT * FROM price_history LIMIT 100;

-- Bad: Fetches everything (can be millions of rows)
SELECT * FROM price_history;

Filter Early

Apply WHERE clauses before JOINs and aggregations:

-- Good: Filter first
SELECT AVG(price)
FROM price_history
WHERE market_hash_name = 'AK-47 | Redline (Field-Tested)'
  AND time > datetime('now', '-7 days');

-- Bad: Scans entire table
SELECT AVG(price)
FROM price_history
WHERE time > datetime('now', '-7 days');

Use Appropriate Data Types

When extracting JSON data, cast to appropriate types:

-- Cast to numeric for calculations
CAST(json_extract(buy_order_table, '$[0].price') AS REAL)

-- Cast to integer for counts
CAST(json_extract(buy_order_table, '$[0].quantity') AS INTEGER)

Database Overview

Learn about the database architecture

price_overview

Current market prices table

orders_histogram

Order book data table

price_history

Historical price data table

Database Schema

Python API

Overview

Connecting to the Database

Basic Queries

Historical Analysis

Order Book Analysis

Activity Analysis

Cross-Item Comparisons

Advanced Queries

JSON Manipulation

Export & Backup

Export to CSV

Backup Database

Performance Tips

Database Overview

price_overview

orders_histogram

price_history

Database Schema

Python API

​Overview

​Connecting to the Database

​Basic Queries

​Historical Analysis

​Order Book Analysis

​Activity Analysis

​Cross-Item Comparisons

​Advanced Queries

​JSON Manipulation

​Export & Backup

​Export to CSV

​Backup Database

​Performance Tips

​Related Resources

Database Overview

price_overview

orders_histogram

price_history

Overview

Connecting to the Database

Basic Queries

Historical Analysis

Order Book Analysis

Activity Analysis

Cross-Item Comparisons

Advanced Queries

JSON Manipulation

Export & Backup

Export to CSV

Backup Database

Performance Tips

Related Resources